import pymysql
import pandas as pd
import uuid
from Database import DatabaseHandler

db_handler = DatabaseHandler(host='localhost', port=3306, user='root', password='123456')
db_handler.create_database('test')
db_handler.cursor.execute('USE test')

# 创建表
sql = """  
CREATE TABLE IF NOT EXISTS Occupation1 (  
    id VARCHAR(225) PRIMARY KEY,  
    Professional_big_class VARCHAR(225),  
    Professional_small_class VARCHAR(225),  
    Professional_name VARCHAR(225)  
)  
"""
db_handler.cursor.execute(sql)
print('表创建成功或已存在')

# 读取Excel文件并准备数据
df = pd.read_excel('C:/Users/姚宇康/Desktop/日常文件/学习/职业信息.xlsx')  # 假设文件路径和工作表正确
df.fillna('', inplace=True)  # 填充空值

# 生成唯一的ID
df['id'] = ['O-' + str(uuid.uuid4())[:8] for _ in range(len(df))]

# 准备插入数据
data_to_insert = df.to_dict(orient='records')

# 插入数据
sql_insert = 'INSERT INTO Occupation1 (Professional_big_class, Professional_small_class, Professional_name,id) VALUES (%s, %s, %s, %s)'
for data in data_to_insert:
    print(data.values())
    try:
        db_handler.cursor.execute(sql_insert, tuple(data.values()))
        # 注意：如果autocommit=False，你需要在这里提交事务
        # db_handler.connection.commit()
        print(db_handler.cursor.rowcount, "记录插入成功。")
    except pymysql.MySQLError as e:
        print(f"数据库错误：{e}")
        # 如果autocommit=False，并且你希望回滚，可以取消注释下面的行
        # db_handler.connection.rollback()

# 关闭连接（通常在类的close_connection方法中处理）
db_handler.close_connection()